﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;

namespace DataAccess
{
    public class CategoryRespository : DataProvider
    {
        /// <summary>
        /// get category table with columns: CatID, CatName, ParrentCatID
        /// </summary>
        /// <returns></returns>
        public DataTable getCategories(){
            String sql = "SELECT c.CatID, c.CatName, c.ParentCatID FROM Categories c";

            return this.executeQueryDataTable(sql);
        }

        public DataTable getCategories(int parentCatID) {
            String sql = "SELECT c.CatID, c.CatName, c.ParentCatID FROM Categories c WHERE c.ParentCatID =" + parentCatID;

            return this.executeQueryDataTable(sql);
        }
        /// <summary>
        /// add new category
        /// </summary>
        /// <param name="catName">name of this category</param>
        /// <param name="parentCatID">parent catid, 0 is no parent</param>
        /// <returns></returns>
        public bool add(String catName, int parentCatID = 0) {
            //_sqlCmd.CommandType = CommandType.Text;
            String sql = String.Format("INSERT INTO Categories(CatName, ParentCatID) VALUES(N'{0}', {1})", catName, parentCatID);
            if (this.executeQuery(sql) > 0) {
                return true;
            }
            return false;
        }

        public bool delete(int catID) {
            String sql = String.Format("DELETE FROM Categories WHERE CatID = {0}", catID);
            if (this.executeQuery(sql) > 0) {
                return true;
            }
            return false;
        }

        /// <summary>
        /// update name and parent cat id
        /// </summary>
        /// <param name="catID"></param>
        /// <param name="catName"></param>
        /// <param name="parentCatID"></param>
        /// <returns></returns>
        public bool update(int catID, String catName, int parentCatID = 0) {
            String sql = String.Format("UPDATE Categories SET CatName = N'{0}', ParentCatID = {1} WHERE CatID = {2}", catName, parentCatID, catID);
            if (this.executeQuery(sql) > 0) {
                return true;
            }
            return false;
        }

    }
}
